%macro get_sched_c_owners;
			
	proc printto new log = "&logdir./get_sched_c_owners.txt";
	run;
	
	proc sql;
		[redacted]
			(
				select distinct
					[redacted] as firm_id,
					[redacted] as person_id,
					[redacted] as year
				from [redacted]
				where year in(2019,2020)
					and [redacted] ~= 0
				order by firm_id, person_id, year
		);
		disconnect from iq;
		quit;
		
	proc printto;
	run;

	
%mend;




%macro get_s_corp_owners;
			
	proc printto new log = "&logdir./get_s_corp_owners.txt";
	run;
	
	proc sql;
		[redacted]
			(
				select 
					[redacted] as person_id,
					[redacted] as firm_id,
					[redacted] as year,
					max(case when [redacted] > 1
						then [redacted]/100
						else [redacted] end) as own_share
				from [redacted]
				where [redacted] in(2019,2020)
					and [redacted] <= 4
					and [redacted] ~= 0
					and [redacted] ~= 0
				group by firm_id, person_id, year
				order by firm_id, person_id, year
		);
		disconnect from iq;
		quit;
		
	proc printto;
	run;

	
%mend;




%macro get_partnership_owners;
			
	proc printto new log = "&logdir./get_partnership_owners.txt";
	run;
	
	
	
	
	proc sql;
		[redacted]
			(
			
			
				select
					max([redacted]) as ord_inc_entity,
					[redacted] as firm_id,
					floor([redacted]/100) as year
				from [redacted]
				where floor([redacted]/100) in(2019,2020)
					and [redacted] > 0
				group by firm_id, year
				order by firm_id, year
		);

			create table k1 as
			select * from connection to iq
			(	
				select 
					[redacted] as person_id,
					[redacted] as firm_id,
					[redacted] as share_begin,
					[redacted] as share_end,
					[redacted] as ord_inc_k1,
					[redacted] as edate,
					[redacted] as year
				from [redacted]
				where [redacted] = 2020
					and [redacted] <= 4
					and [redacted] > 0
					and [redacted] > 0
				order by firm_id, year, person_id, edate
			
				
		);
		disconnect from iq;
		quit;
		
		
		data k1;
			set k1;
			by firm_id year person_id;
			if last.person_id;
			drop edate;
		run;
		
		
		data rlib.partnership_owners;
			merge k1(in=a) rlib.f1065(in=b);
			by firm_id year;
			if a & b;
			
			own_share = share_begin;
			if own_share = 0 | own_share = . then own_share = share_end;
			if own_share > 1 then own_share = own_share/100;
			if (own_share = 0 | own_share = .) &
				sign(ord_inc_k1) = sign(ord_inc_entity) & 
					abs(ord_inc_entity) >= abs(ord_inc_k1)
					& abs(ord_inc_entity) > 0 then
						own_share = ord_inc_k1/ord_inc_entity;
			
			if own_share = 0 | own_share = . then delete;
			
			keep person_id firm_id own_share year;
		run;
						
		
	proc printto;
	run;

	
%mend;





%macro get_c_corp_owners;
			
	proc printto new log = "&logdir./get_c_corp_owners.txt";
	run;
	
	proc sql;
		[redacted]
			(
				select 
					a.[redacted] as child_flr_tin, 
					a.[redacted] as child_corp_tin,
					c.[redacted] as person_id1,
					c.[redacted] as person_id2,
					c.[redacted] as own_share,
					floor(a.[redacted]/10000) as year
				from [redacted] as a
					INNER JOIN [redacted] as b
						on (a.[redacted] = b.[redacted]
			    				and a.[redacted] = b.[redacted]
			    				and a.[redacted] = b.[redacted])
					INNER JOIN [redacted] as c
						on (a.[redacted] = c.[redacted]
			   			 	and a.[redacted] = c.[redacted]
			    				and a.[redacted] = c.[redacted]
			    				and b.[redacted] = c.[redacted])
					WHERE a.[redacted] between 20190000 and 20201231
					and substr(a.[redacted],15,5) = "00000"
		);
		disconnect from iq;
		quit;
		
		data c_corp_owners;
			set sample;
			person_id = max(person_id1,person_id2);
			firm_id = max(child_flr_tin,child_corp_tin);
			if own_share = 0 | own_share = . then own_share = 1;
			if own_share > 1 then own_share = own_share/100;
			keep firm_id person_id own_share year;
		run;
		proc means data = c_corp_owners nway noprint;
			class firm_id person_id year;
			output out = rlib.c_corp_owners(drop=_type_ _freq_)
				max(own_share) = own_share;
		run;
		
	proc printto;
	run;

	
%mend;



%macro assemble_owners;

proc printto new log = "&logdir./assemble_owners.txt";
	run;
	
	data appended;
		set rlib.sched_c_owners
			rlib.s_corp_owners
			rlib.c_corp_owners
			rlib.partnership_owners;
		if own_share = . then own_share = 1;
	run;
	
	proc means data = appended nway noprint;
		class firm_id person_id year;
		output out = rlib.firm_owners(drop=_type_ _freq_)
			max(own_share) = own_share;
	run;
	
	proc printto;
	run;


%mend;
